Step 1 - Text file with the new columns
In
the zip file that you extracted in the previous Step 9, open the text file, WakeCountyRealEstateProperty_1990-2020_CalculatedColumns_v01.txt, that has the formulas to
create columns. The file is located in the DAX_Measures_Columns folder.
Step 2 -Load Data
With the Power BI Report file open, click on Home -> Get Data -> and under Common Data Sources, select Excel. Browse to the dataset folder and select the Excel files, WakeCounty_Real_Estate_Parcel_Identification_v02.xlsx -> Select the worksheet and click load.
Follow the above steps for all the Microsoft Excel, .xlsx, files:
WakeCounty_Real_Estate_Property_1990-2020_v02.xlsx
WakeCounty_Real_Estate_Owner_v02.xlsx
WakeCounty_Real_Estate_PhysicalAddress_v02.xlsx
WakeCounty_Real_Estate_Story_Height_v02.xlsx
WakeCounty_Real_Estate_Township_v02.xlsx
WakeCounty_Real_Estate_Type_and_Use_v02.xlsx
The tables will be loaded and appear on the right-hand side of the window pane.
For the Township spreadsheet, be sure to select the Township worksheet:
And select PhysicalAddress spreadsheet, in the PhysicalAddress worksheet:
Also, select Owner spreadsheet for the Real_Estate_Owner spreadsheet:
Also, select Parcel_Identification spreadsheet, in the Parcel_Identification worksheet.
Also, Property in the Property worksheet and
Once the table is loaded, click on the table,Property, on right-hand side of the window pane.
Also, Story_Height worksheet in the Story_Height spreadsheet.
Note:
If the loaded table does not have the correct column names, if the first row is not detected as the header, click on the Home ribbon -> Transform Data -> e.g., select Story_Height .
Select Use First Row as Headers. To save the changes, click on Close & Apply.
Step 3 - Create the new columns that will be needed
Open the text file, found in the folder DAX_Measures_Columns, the file is named WakeCountyRealEstateProperty_1990-2020_CalculatedColumns_v01.txt
After clicking on the table, Property, click to Table Tools -> New Column and add all the columns in the text file.
The following example is how to create the file column, Size_Range_Residential.
Follow the same steps to create the remainder new columns.
Note: If you don't see the option to add a new column, be sure you are in the 'Table Tools' tab. Also, be sure to click on 'New Column' first, to avoid overwriting your previously created column(s).
Verify all columns were created successfully:
Step 4: Create a DateKey column:
In order to set up a Date hierarchy, we need to set up a DateKey column.
Open the Property table, duplicate the Total_Sale_Date Column
Rename the new column DateKey
Click on the new DateKey column and verify hierarchy has been set up.
Step 5: Create a Date Dimension Table:
Open the DateDimensionTable.txt file. Copy all the lines in the text file.
Still in the Data view, create a new table and paste the code you previously copied.
Sort DateDimension Table
In order for the months to be sorted chronologically rather than alphabetically, click on the Table Tools -> click on Data View ->
In the Fields pane, ensure that the MonthAbbr field is selected (when selected, it will have a dark gray background).
On the Column Tools contextual ribbon, from inside the Sort group, click Sort by Column, and then select MonthNumber.
Do the same for the rest of the columns:
Highlight MonthFull -> Sort by Column -> MonthNumber
Highlight DayOfWeekFull -> Sort by Column -> Weekday_Number
Highlight WeekDay -> Sort by Column -> Weekday_Number
Highlight WeekDayShort -> Sort by Column -> Weekday_Number
